﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Dashboard.Expense.Business
{
    public class Exp_ExpenseLimit : BusinessBase
    {
        public DataTable QueryExpenseLimits(string appTypeCode, string expenseCode)
        {
            string sql = "select * from exp_feelimit where 1=1 ";

            if (!string.IsNullOrEmpty(appTypeCode))
            {
                sql += " and apptypecode = '" + appTypeCode + "'";
            }

            if (!string.IsNullOrEmpty(expenseCode))
            {
                sql += " and feecode = '" + expenseCode + "'";
            }

            sql += " order by apptypecode,feecode";

            return DbConnection.DataSet(sql).Tables[0];
        }

        public void DeleteExpenseLimit(int id)
        {
            string sql = "delete from exp_feelimit where id=" + id;
            DbConnection.ExecuteSql(sql);
        }

        public DataTable GetAllPositions()
        {
            string sql = "select distinct position from namelist order by position";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetAllLevels(string position)
        {
            string sql = "select distinct level from namelist where position='" + position.Replace("'","''") + "' order by level";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public void InsertExpenseLimit(string sql)
        {
            DbConnection.ExecuteSql(sql);
        }

        public void UpdateExpenseLimit(string sql)
        {
            DbConnection.ExecuteSql(sql);
        }

        public DataTable GetExpenseLimit(int id)
        {
            string sql = "select * from exp_feelimit where id=" + id;
            return DbConnection.DataSet(sql).Tables[0];
        }

        public bool FormLimitExists(string applicationType, string expenseType, string location, string position, string level)
        {
            string sql = "select * from exp_feelimit where apptypecode='{0}' and feecode='{1}' and location='{2}' and position='{3}' and level='{4}'";
            sql = string.Format(sql, applicationType, expenseType, location, position, level);

            DataTable result = DbConnection.DataSet(sql).Tables[0];

            if (result.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public DataTable GetExpenseLimit(string applicationType, string expenseType, string location, string position, string level)
        {
            string sql = "select * from exp_feelimit where apptypecode='{0}' and feecode='{1}' and location='{2}' and position='{3}' and level='{4}'";
            sql = string.Format(sql, applicationType, expenseType, location.Replace("'","''"), position, level);

            DataTable result = DbConnection.DataSet(sql).Tables[0];

            return result;
        }
    }
}
